package com.F06.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.log4j.Logger;

import com.F06.actionForm.F060101ActionForm;
import com.dao.common.DbOperationExt;
import com.dbtools.Dispose;
import com.util.CommonTools;

/**
 * @wangk071226
 * @综合统计下载
 * @功能：生成数据倒入EXCEL
 */
public class F060101DAO extends DbOperationExt {

	/**
	 * 记录操作日志;
	 */
	Logger log = Logger.getLogger(F060101DAO.class);

	/**
	 * 构造方法获取数据源;
	 * 
	 */
	public F060101DAO() {
		super();
	}

	public F060101DAO(Connection conn) {
		super(conn);
	}

	/**
	 * @第一页数据
	 */
	public List sheet0(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		msql.append(" select a.cotract_number as '订单号',");
		msql.append("        a.manufacturing_order as '工作号',");
		msql.append("        adyh.FUN_JSDH(a.manufacturing_order) as '技术代号',");
		msql.append("        adyh.FUN_CPXH(a.manufacturing_order) as '型号',");
		msql.append("        adyh.FUN_GG(a.manufacturing_order) as '规格',");
		msql.append("        a.quantity as '数量',");
		msql.append("        CAST ((a.unit_price * a.VAT + a.unit_price) as int) *(0.00001) as '合同单位1',");
		msql.append("        adyh.FUN_C_CUSTOMER_TYPE(a.customer_type) as '客户类别',");
		msql.append("        CAST ((a.unit_price * a.VAT + a.unit_price) as int) as '合同单位2',");
		msql.append("        a.unit_price as '单价',");
		msql.append("        a.unit_price * a.quantity as '合同合计不含税',");
		msql.append("        adyh.FUN_QMSL(a.manufacturing_order, '"+time+"') as '期初数量',");
		msql.append("        adyh.FUN_SCSL(a.manufacturing_order, '"+time+"') as '生产数量',");
		msql.append("        adyh.FUN_GETProductCost(a.manufacturing_order) as  '生产成本',");
		msql.append("        adyh.FUN_FPJL(a.manufacturing_order) as '发票号码',");
		msql.append("        adyh.FUN_FPSL(a.manufacturing_order) as '发票数量'");
		msql.append(" from c_orders a where ");
		msql.append(" a.signaure_date BETWEEN adyh.FUN_YC_TIME('"+time+"') and ");
        msql.append(" adyh.FUN_YM_TIME('"+time+"') ");
		try {
			log.info("综合统计下载 sheet0 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("订单号", CommonTools.null2String(rest.getString("订单号")));
				map.put("工作号", CommonTools.null2String(rest.getString("工作号")));
				map.put("技术代号", CommonTools.null2String(rest.getString("技术代号")));
				map.put("型号", CommonTools.null2String(rest.getString("型号")));
				map.put("规格", CommonTools.null2String(rest.getString("规格")));
				map.put("数量", CommonTools.null2String(rest.getString("数量")));
				map.put("合同单位1", CommonTools.null2String(rest.getString("合同单位1")));
				map.put("客户类别", CommonTools.null2String(rest.getString("客户类别")));
				map.put("合同单位2", CommonTools.null2String(rest.getString("合同单位2")));
				map.put("单价", CommonTools.null2String(rest.getString("单价")));
				map.put("合同合计不含税", CommonTools.null2String(rest.getString("合同合计不含税")));
				map.put("期初数量", CommonTools.null2String(rest.getString("期初数量")));
				map.put("生产数量", CommonTools.null2String(rest.getString("生产数量")));
				map.put("生产成本", CommonTools.null2String(rest.getString("生产成本")));
				map.put("发票号码", CommonTools.null2String(rest.getString("发票号码")));
				map.put("发票数量", CommonTools.null2String(rest.getString("发票数量")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @第二页数据
	 */
	public List sheet1(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;
		msql.append(" select DATENAME(Year, a.invoice_date) + N'年' + ");
		msql.append("        CAST (DATEPART(Month,a.invoice_date) AS ");
		msql.append(" 	   varchar) + N'月' as  '月份', ");
		msql.append("        adyh.FUN_C_CUSTOMERS(b.customer_code) as '客户名称', ");
		msql.append("        b.manufacturing_order as '工作号', ");
		msql.append("        adyh.FUN_CPXH(a.manufacturing_order) as '产品型号',");
		msql.append("        a.qty as '数量',");
		msql.append("        a.unit_price as '单价',");
		msql.append("        (a.qty *(a.unit_price *(b.VAT * 0.01 + 1))) as '含税金额',");
		msql.append("        (a.qty * a.unit_price) as '不含税金额',");
		msql.append("        REPLACE(CONVERT(varchar(10),a.invoice_date,120),N'-0','-') as '开票日期',");
		msql.append("        a.invoice_number as '发票号',");
		msql.append("        adyh.FUN_SEND_DATE(a.manufacturing_order) as '发货日期' ");
		msql.append(" from f_invoicing_production a ");
		msql.append("      left join c_orders b on a.manufacturing_order = b.manufacturing_order ");
		msql.append(" where a.invoice_date BETWEEN adyh.FUN_YC_TIME('"+time+"') and ");
        msql.append(" adyh.FUN_YM_TIME('"+time+"') ");
		
		try {
			log.info("综合统计下载 sheet1 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("月份", CommonTools.null2String(rest
						.getString("月份")));
				map.put("客户名称", CommonTools.null2String(rest
						.getString("客户名称")));
				map.put("工作号", CommonTools.null2String(rest
						.getString("工作号")));
				map.put("产品型号", CommonTools.null2String(rest
						.getString("产品型号")));
				map.put("数量", CommonTools.null2String(rest
						.getString("数量")));
				map.put("单价", CommonTools.null2String(rest
						.getString("单价")));
				map.put("含税金额", CommonTools.null2String(rest
						.getString("含税金额")));
				map.put("不含税金额", CommonTools.null2String(rest
						.getString("不含税金额")));
				map.put("开票日期", CommonTools.null2String(rest
						.getString("开票日期")));
				map.put("发票号", CommonTools.null2String(rest
						.getString("发票号")));
				map.put("发货日期", CommonTools.null2String(rest
						.getString("发货日期")));

				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}

	/**
	 * @第三页数据
	 */
	public List sheet2(String time) {
		StringBuffer msql = new StringBuffer();
		List list = new ArrayList();
		Map map = null;
		ResultSet rest = null;

		msql.append(" select adyh.FUN_C_CUSTOMERS(a.customer_code) as '客户名称',");
		msql.append("       adyh.FUN_CPXH(a.manufacturing_order) as '产品型号',");
		msql.append("       DATENAME(Year, a.signaure_date) + N'年' + CAST (DATEPART(Month,");
		msql.append("        a.signaure_date) AS varchar) + N'月' as '月份',");
		msql.append("       a.quantity as '数量',");
		msql.append("       a.unit_price as '单价',");
		msql.append("       adyh.FUN_GETProductCost(a.manufacturing_order) as '外购单价',");
		msql.append("       cast ((a.quantity * a.unit_price) /(1 + a.VAT * 0.01) as numeric (18, 2))");
		msql.append("        as '外购收入不含税',");
		msql.append("       a.quantity * cast (adyh.FUN_GETProductCost(a.manufacturing_order) as");
		msql.append("        float) as '外购成本含税',");
		msql.append("       cast ((a.quantity * cast (adyh.FUN_GETProductCost(a.manufacturing_order)");
		msql.append("        as float)) /(1 + a.VAT * 0.01) as numeric (18, 2)) as '外购成本'");
		msql.append(" from c_orders a");
		msql.append(" where a.own_production = '0' and ");
		msql.append(" a.signaure_date BETWEEN adyh.FUN_YC_TIME('"+time+"') and ");
        msql.append(" adyh.FUN_YM_TIME('"+time+"') ");

		try {
			log.info("综合统计下载 sheet2 sql is " + msql.toString());
			rest = super.executeQuery(msql.toString());
			while (rest.next()) { // 循环结果集并在map中添加结果集每行对应字段的值;
				map = new HashMap();
				map.put("客户名称", CommonTools.null2String(rest
						.getString("客户名称")));
				map.put("产品型号", CommonTools.null2String(rest
						.getString("产品型号")));
				map.put("月份", CommonTools.null2String(rest
						.getString("月份")));
				map.put("数量", CommonTools.null2String(rest
						.getString("数量")));
				map.put("单价", CommonTools.null2String(rest
						.getString("单价")));
				map.put("外购单价", CommonTools.null2String(rest
						.getString("外购单价")));
				map.put("外购收入不含税", CommonTools.null2String(rest
						.getString("外购收入不含税")));
				map.put("外购成本含税", CommonTools.null2String(rest
						.getString("外购成本含税")));
				map.put("外购成本", CommonTools.null2String(rest
						.getString("外购成本")));
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			Dispose.dispose(rest); // 关闭rest及statement;
		}

		return list;
	}
}
